package cn.lanqiao.dao.daoImpl;

import cn.lanqiao.dao.StudentDao;
import cn.lanqiao.entity.Student;

import java.sql.*;
import java.util.ArrayList;



public class StudentDaoImpl implements StudentDao {
    /*
    mvc 三层架构 view 视图层 --看到的界面（相当于菜单）
               service 服务层 --放成员变量记录菜单的内容 （相当于服务员把客户想要的菜记录下来）
               dao 持久层 --真正执行的地方（相当于厨师炒菜）
    总结：
    1、没有引用 会报错
    2、接口 放成员变量或者方法---实现类 ---implements
    3、查询--返回集合 增删改--返回的数字 i
    4、
    * */
//增加学生
    @Override
    public int addStudent(Student s) {
        //在永久层里面写真操作的东西
        Connection conn = null;
        int i = 0;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            //定义sql语句
            String sql = "insert into students values (?,?,?,?)";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //设置参数 参数1--问好的下标 参数2--问好代替的内容
//            prep.setInt(1,s.getStudent_id());
            prep.setInt(1,s.getStudent_id());
            prep.setString(2,s.getName());
            prep.setInt(3,s.getAge());
            prep.setString(4, s.getGender());
            i = prep.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try{
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return i;
    }
//查询学生
    @Override
    public ArrayList<Student> queryAllStudent() {
        Connection conn = null;
        ArrayList<Student> student = null;
        //
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            String sql = "select * from students";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //执行
            ResultSet res = prep.executeQuery();
            //创建一个集合 用于存放数据
            student = new ArrayList<>();
            while (res.next()){
                //获取相应字段
                int student_id = res.getInt("student_id");
                String name = res.getString("name");
                int age = res.getInt("age");
                String gender = res.getString("gender");
                //放入学生对象中
                Student st = new Student();
                st.setStudent_id(student_id);
                st.setName(name);
                st.setAge(age);
                st.setGender(gender);
                //添加到集合
                student.add(st);
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try{
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return student;
    }
    //按照学号查找学生
    @Override
    public Student quertStudentById(int parseInt) {
        Connection conn = null;
        ArrayList<Student> student = null;
        Student st = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            //定义sql
            String sql = "select * from students where student_id = ?";
            //获取pstmtduix
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.setInt(1,parseInt);
            //执行
            ResultSet res = prep.executeQuery();
            //创建一个集合 用于存放数据
            student = new ArrayList<>();
            //查询
            while (res.next()){
                //获取相应的字段
                int student_id = res.getInt("student_id");
                String name = res.getString("name");
                int age = res.getInt("age");
                String gender = res.getString("gender");
                //放入学生对象中
                st = new Student();
                st.setGender(gender);
                st.setAge(age);
                st.setName(name);
                st.setStudent_id(student_id);
                //添加到集合
                student.add(st);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        finally {
            try{
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return st;
    }
//修改学生信息
    @Override
    public int updateStudentInfo(Student student) {
        Connection conn = null;
        int i = 0;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            //定义sql
            String sql = "update students set name = ? , age = ? , gender = ?  where student_id = ? ";
            //获取prstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //设置参数
            prep.setString(1,student.getName());
            prep.setInt(2,student.getAge());
            prep.setString(3,student.getGender());
            prep.setInt(4,student.getStudent_id());
            //执行
            i = prep.executeUpdate();

        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try{
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return i;
    }
//删除学生
    @Override
    public int deleteStudent(int student_id) {
        Connection conn = null;
        int i = 0;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            //定义sql语句
            String sql = "delete from students where student_id = ?";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //设置参数 参数1--问好的下标 参数2--问好代替的内容
            prep.setInt(1,student_id);
            i = prep.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try{
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return i;
    }
//分页查询
    @Override
    public ArrayList<Student> queryStudentBypage(int pageNum, int pageSize) {
        Connection conn = null;
        ArrayList<Student> student = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            //定义sql
            String sql = "select * from students LIMIT ?, ?  ";
            //获取pstmtduix
            PreparedStatement prep = conn.prepareStatement(sql);
            prep.setInt(1,(pageNum - 1) * pageSize);
            prep.setInt(2,pageSize);
            //执行
            ResultSet res = prep.executeQuery();
            //创建一个集合 用于存放数据
            student = new ArrayList<>();
            //查询
            while (res.next()){
                //获取相应的字段
                int student_id = res.getInt("student_id");
                String name = res.getString("name");
                int age = res.getInt("age");
                String gender = res.getString("gender");
                //放入学生对象中
                Student stu = new Student();
                stu.setGender(gender);
                stu.setAge(age);
                stu.setName(name);
                stu.setStudent_id(student_id);
                //添加到集合
                student.add(stu);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        finally {
            try{
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return student;
    }
//排序
    @Override
    public ArrayList<Student> orderAllStudent() {
        Connection conn = null;
        ArrayList<Student> student = null;
        //
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            String sql = "select * from students order by age";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //执行
            ResultSet res = prep.executeQuery();
            //创建一个集合 用于存放数据
            student = new ArrayList<>();
            while (res.next()){
                //获取相应字段
                int student_id = res.getInt("student_id");
                String name = res.getString("name");
                int age = res.getInt("age");
                String gender = res.getString("gender");
                //放入学生对象中
                Student st = new Student();
                st.setStudent_id(student_id);
                st.setName(name);
                st.setAge(age);
                st.setGender(gender);
                //添加到集合
                student.add(st);
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try{
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return student;
    }
//统计
    @Override
    public int totalStudents() {
        int total = 0;
        Connection conn = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            String sql = "SELECT COUNT(*) FROM students";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //执行
            ResultSet res = prep.executeQuery();
            while (res.next()){
                total = res.getInt(1);
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
        return total;
    }

    @Override
    public int femaleStudents() {
        int femaleCount = 0;
        Connection conn = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            String sql = "SELECT COUNT(*) FROM students WHERE gender = '女'";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //执行
            ResultSet res = prep.executeQuery();
            while (res.next()){
                femaleCount = res.getInt(1);
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
        return femaleCount;
    }

    @Override
    public int maleStudents() {
        int maleCount = 0;
        Connection conn = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            String sql = "SELECT COUNT(*) FROM students WHERE gender = '男'";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //执行
            ResultSet res = prep.executeQuery();
            while (res.next()){
                maleCount = res.getInt(1);
            }
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
        return maleCount;
    }
//批量操作
    @Override
    public void batchAddStudents(ArrayList<Student> students) {
        Connection conn = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            String sql = "INSERT INTO students (student_id, name, age,gender) VALUES (?,?,?,?)";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //执行
            for (Student student : students) {
                prep.setInt(1,student.getStudent_id());
                prep.setString(2, student.getName());
                prep.setInt(3, student.getAge());
                prep.setString(4,student.getGender());
                prep.addBatch();
            }
            prep.executeBatch();
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void batchDeleteStudents(ArrayList<Integer> student_id) {
        Connection conn = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //实现连接 shixun--数据库名称
            String url = "jdbc:mysql://localhost:3306/qimolx";
            String user = "root";
            String pwd = "root";
            conn = DriverManager.getConnection(url,user,pwd);
            String sql =  "DELETE FROM students WHERE student_id =?";
            //获取pstmt对象
            PreparedStatement prep = conn.prepareStatement(sql);
            //执行
            for (Integer studentIds : student_id) {
                prep.setInt(1,studentIds);
                prep.addBatch();
            }
            prep.executeBatch();
        }catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

}
